package com.mx.kn.cstf2.dao.impl;


import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import kn.cstf2.transition.Bean.AsignIfownerStkBean;
import kn.cstf2.transition.Bean.DocumentsBean;
import kn.cstf2.transition.Bean.JoinPersonTransitionBean;
import kn.cstf2.transition.Bean.Localidad;
import kn.cstf2.transition.Bean.PersonalBean;
import kn.cstf2.transition.Bean.T_clientesDataFromStkBean;
import kn.cstf2.transition.Bean.TransitionBean;
import kn.mx.cstf2.sql.TransitionSql;

import org.jfree.util.Log;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;

import com.mx.kn.cstf2.dao.TransitionDao;
import com.mx.kn.cstf2.dto.HistoricoDto;
import com.mx.kn.cstf2.dto.UsuarioDto;
import com.mx.kn.cstf2.dts.FilesUploadDts;
import com.mx.kn.cstf2.dts.JoindocumenttransitionDts;
import com.mx.kn.cstf2.dts.PersonOptions;

@Repository
@Component("transitionDao")
public class TransitionDaoImpl implements TransitionDao {
	private JdbcTemplate jdbcStksf2;
	private JdbcTemplate jdbcCustomerSalesToolKit;

	@Autowired
	public void setDataSourceStkfsf2(
			@Qualifier("dataSourceStkfsf2") DataSource DataSourceStkfsf2) {
		this.jdbcStksf2 = new JdbcTemplate(DataSourceStkfsf2);
	}

	@Autowired
	public void setDataSourceCustomerSalesToolKit(
			@Qualifier("dataSourceCustomerSalesToolKit") DataSource dataSourceCustomerSalesToolKit) {
		this.jdbcCustomerSalesToolKit = new JdbcTemplate(
				dataSourceCustomerSalesToolKit);

	}


	public ArrayList<String> getListaNombreDePersonal() {
		ArrayList<String> names = new ArrayList<String>();
		try {
			List<Map<String, Object>> rows = jdbcStksf2
					.queryForList(TransitionSql.SELECCIONAR_NOMBRE_PERSONAL);
			for (Map row : rows) {
				names.add((String) row.get("name"));
			}

		} catch (Exception e) {
			Log.error(e);
		}

		return names;

	}

	@Override
	public TransitionBean obtenerTransitionBeanByKnkeyAndId(
			TransitionBean transitionBean)  throws SQLException{
//		TransitionBean returnTransitionBean = new TransitionBean();
//		try {
//			List<Map<String, Object>> rows = jdbcStksf2.queryForList(
//					TransitionSql.SELECCIONAR_IDKNKEY,
//					new Object[] { transitionBean.getKnkey() });
//			for (Map row : rows) {
//				returnTransitionBean.setId((Integer) row.get("id"));
//				returnTransitionBean.setKnkey((Integer) row.get("knkey"));
//				returnTransitionBean.setStatus((String) row.get("status"));
//			}
//
//		} catch (Exception e) {
//			Log.error(e);
//		}
		String sql = "select * from t_transitions where knkey = ? AND \"tipoTransition\" = ?";
		 
		return (TransitionBean)jdbcStksf2.queryForObject(
				sql, new Object[] { transitionBean.getKnkey(),transitionBean.getTipoTransition()}, new BeanPropertyRowMapper(TransitionBean.class));
		


	}

	
	@Override
	public FilesUploadDts verifyUploaded(TransitionBean transition) throws SQLException {

		FilesUploadDts filesUpload = new FilesUploadDts();
		try {
			List<Map<String, Object>> rows = jdbcStksf2.queryForList(
					TransitionSql.SELECCIONAR_FILES_UPLOADED,
					new Object[] { transition.getKnkey(), transition.getTipoTransition() });
			for (Map row : rows) {

				switch ((Integer) row.get("iddocument")) {
				case FilesUploadDts.ACON_DEBTOR_NUMBER:
					filesUpload.setAconDebtorNumber((Boolean) row
							.get("uploaded"));
					break;

				case FilesUploadDts.CUSTOMER_PROFILE:
					filesUpload.setCustomerProfile((Boolean) row
							.get("uploaded"));
					break;

				case FilesUploadDts.CUSTOMER_QUOTATION:
					filesUpload.setCustomerQuoation((Boolean) row
							.get("uploaded"));
					break;

				case FilesUploadDts.R1_R2:
					filesUpload.setR1_r2((Boolean) row.get("uploaded"));
					break;

				case FilesUploadDts.SALES_CALL:
					filesUpload.setSalesCall((Boolean) row.get("uploaded"));
					break;

				case FilesUploadDts.RATE_TRACKER:
					filesUpload.setRateTracker((Boolean) row.get("uploaded"));
					break;

				case FilesUploadDts.RFC:
					filesUpload.setRfc((Boolean) row.get("uploaded"));
					break;

				default:
					break;
				}

			}

		} catch (Exception e) {
			Log.error(e);
		}

		return filesUpload;
	}

	public TransitionBean obtenerJoindocumenttransitionDetail(int RATE_TRACKER,
			int ACON_DEBTOR_NUMBER, Integer transitionId) throws SQLException {
		TransitionBean transitionBean = new TransitionBean();
		try {
			List<Map<String, Object>> rows = jdbcStksf2.queryForList(
					TransitionSql.SELECCIONAR_JOINDOCUMENTTRANSITIONDETAIL,
					new Object[] { RATE_TRACKER, ACON_DEBTOR_NUMBER ,transitionId});
			for (Map row : rows) {

				switch ((Integer) row.get("iddocument")) {
				case FilesUploadDts.ACON_DEBTOR_NUMBER:
					transitionBean.setAcondebtornumbertext((String) row
							.get("details"));
					break;

				case FilesUploadDts.RATE_TRACKER:
					transitionBean.setRatetrackertext((String) row
							.get("details"));
					break;

				default:
					break;
				}

			}

		} catch (Exception e) {
			Log.error(e);
		}
		return transitionBean;
	}

	public String obtenerTransitionLog(TransitionBean transition) throws SQLException {

		return (String) jdbcStksf2.queryForObject(
				TransitionSql.OBTENER_TRANSITION_LOG, new Object[] { transition.getKnkey(), transition.getTipoTransition() },
				String.class);

	}

	public void actualizarLogEnTransition(TransitionBean transitionBean)
			throws SQLException {

		int rows = jdbcStksf2
				.update(TransitionSql.UPDATE_LOGTRANSITION, new Object[] {
						transitionBean.getLog(), transitionBean.getId() });

	}

	
	public T_clientesDataFromStkBean obtenerDataFromTClientesSTK(
			TransitionBean transitionBean) throws SQLException {
		T_clientesDataFromStkBean t_clientesDataFromStkBean = new T_clientesDataFromStkBean();

		List<Map<String, Object>> rows = jdbcCustomerSalesToolKit.queryForList(
				TransitionSql.SELECCIONAR_DATAFROMKNKEYFROMSTK,
				new Object[] { transitionBean.getKnkey() });

		for (Map row : rows) {
			t_clientesDataFromStkBean.setCompanyname((String) row
					.get("companyname"));
			t_clientesDataFromStkBean.setComercialname((String) row
					.get("comercialname"));
			t_clientesDataFromStkBean.setCompaddress((String) row
					.get("compaddress"));
			t_clientesDataFromStkBean.setCiudad((String) row.get("ciudad"));
			;
		}
		return t_clientesDataFromStkBean;
	}

	
	public AsignIfownerStkBean obtenerIfOwnerFromSTK(
			TransitionBean transitionBean) throws SQLException {

		AsignIfownerStkBean asignIfownerStkBean = new AsignIfownerStkBean();
		try{
			asignIfownerStkBean.setIfownwer((String) jdbcCustomerSalesToolKit
					.queryForObject(TransitionSql.SELECCIONAR_IFOWNERFROMKNKEY,
							new Object[] { transitionBean.getKnkey() },
							String.class));
		}catch(EmptyResultDataAccessException emptyE){
			asignIfownerStkBean.setIfownwer("");
		}
		
		return asignIfownerStkBean;
	}

	
	public JoindocumenttransitionDts selectVoBoEnJoindocumenttransition(
			Integer idtransition) throws SQLException {
		JoindocumenttransitionDts joindocumenttransitionBean = new JoindocumenttransitionDts();
		ArrayList<Boolean> checboxVobo = new ArrayList<Boolean>();

		List<Map<String, Object>> rows = jdbcStksf2.queryForList(
				TransitionSql.SELECT_VOBO_EN_JOINDOCUMENTTRANSITION,
				new Object[] { idtransition });
		for (Map row : rows) {

			switch ((Integer) row.get("iddocument")) {
			case FilesUploadDts.ACON_DEBTOR_NUMBER:
				checboxVobo.add((Boolean) row.get("vobo"));
				break;

			case FilesUploadDts.CUSTOMER_PROFILE:
				checboxVobo.add((Boolean) row.get("vobo"));
				break;

			case FilesUploadDts.CUSTOMER_QUOTATION:
				checboxVobo.add((Boolean) row.get("vobo"));
				break;

			case FilesUploadDts.R1_R2:
				checboxVobo.add((Boolean) row.get("vobo"));
				break;

			case FilesUploadDts.SALES_CALL:
				checboxVobo.add((Boolean) row.get("vobo"));
				break;

			case FilesUploadDts.RATE_TRACKER:
				checboxVobo.add((Boolean) row.get("vobo"));
				break;

			case FilesUploadDts.RFC:
				checboxVobo.add((Boolean) row.get("vobo"));
				break;
			default:
				break;
			}

		}
		joindocumenttransitionBean.setCheckboxesVoBo(checboxVobo);
		return joindocumenttransitionBean;
	}

	
	public ArrayList<PersonalBean> obtenerDataPersonalByIdPersonal(Integer idp)
			throws SQLException {
		PersonalBean personalBean = new PersonalBean();
		ArrayList<PersonalBean> personalListbyKnkeyTransition = new ArrayList<PersonalBean>();

		List<Map<String, Object>> rows = jdbcStksf2.queryForList(
				TransitionSql.SELECCIONAR_DATAPERSONAL_BYIDP,
				new Object[] { idp });
		for (Map row : rows) {
			personalBean = new PersonalBean();
			personalBean.setId((Integer) row.get("id"));
			personalBean.setBf((String) row.get("bf"));
			personalBean.setName((String) row.get("name"));
			personalBean.setEmail((String) row.get("email"));
			personalBean.setPhone((String) row.get("phone"));
			personalBean.setKnkey((Integer) row.get("knkey"));
			personalListbyKnkeyTransition.add(personalBean);
		}
		return personalListbyKnkeyTransition;
	}

	
	public ArrayList<Localidad> branch() throws SQLException {
		ArrayList<Localidad> localidades = new ArrayList<Localidad>();
		String query = "SELECT nombre FROM localidad";
		List<Map<String, Object>> rows = jdbcStksf2.queryForList(query);
		for (Map row : rows) {
			Localidad localidad = new Localidad();
			localidad.setNombre((String) row.get("nombre"));
			localidades.add(localidad);
		}

		return localidades;
	}

	
	public String getLocality(Integer knkey) throws SQLException {
		String query = "SELECT states FROM t_transitions where knkey = ?";
		String msj = (String) jdbcStksf2.queryForObject(query,
				new Object[] { knkey }, String.class);
		String aviso = "";
		if (msj == null)
			return null;
		else {
			aviso += msj + ", ";
		}

		return aviso.substring(0, aviso.length() - 2);
	}

	
	public String validateBranch(Integer knkey) throws SQLException {
		String query = "SELECT confirmated FROM t_transitions where knkey = ?";
		String msj = "";
		Boolean con = false;
		String aviso = null;
		String local = "";

		con = (Boolean) jdbcStksf2.queryForObject(query,
				new Object[] { knkey }, Boolean.class);
		if (con) {
			local += msj + ", ";
		}

		if (!local.equals("")) {
			local = local.substring(0, local.length() - 2);
			aviso = "Las siguientes localidades ya fueron notificadas: "
					+ local;
		}
		return aviso;
	}

	
	public JoindocumenttransitionDts obtenerJoindocumenttransitionData(
			TransitionBean transitionBean) throws SQLException {
		JoindocumenttransitionDts joindocumenttransitionBean = new JoindocumenttransitionDts();
		List<Map<String, Object>> rows = jdbcStksf2.queryForList(
				TransitionSql.SELECCIONAR_JOINDOCUMENTTRANSITIONDATA,
				new Object[] { transitionBean.getId() });
		for (Map row : rows) {
			joindocumenttransitionBean.setVobo((Boolean) row.get("vobo"));
			joindocumenttransitionBean.setUploaded((Boolean) row
					.get("uploaded"));
			joindocumenttransitionBean.setDetails((String) row.get("details"));
		}
		return joindocumenttransitionBean;
	}

	
	public void updateCheckbox(
			JoindocumenttransitionDts joindocumenttransitionBean,
			int iddocument) throws SQLException {

		DateFormat dateFormat = new SimpleDateFormat("HH:mm:ss dd/MM/yyyy");
		String fecha = dateFormat.format(new Date());

		int rows = jdbcStksf2.update(
				TransitionSql.UPDATE_VOBO_CHECKBOX,
				new Object[] { joindocumenttransitionBean.getVobo(), fecha,
						iddocument,
						joindocumenttransitionBean.getIdtransition() });

	}

	
	public Map<Integer,Boolean> checkCheckbox(
			JoindocumenttransitionDts joindocumenttransitionBean) throws SQLException {

		Map<Integer,Boolean> checkBoxs = new HashMap<Integer,Boolean>();
		
         List<Map<String, Object>> rows = jdbcStksf2.queryForList(TransitionSql.SELECT_VOBO_EN_JOINDOCUMENTTRANSITION,
				new Object[] { joindocumenttransitionBean.getIdtransition()  });
		for (Map row : rows) {
			checkBoxs.put((Integer)row.get("iddocument"),(Boolean)row.get("vobo"));	
		}
//        if(joindocumenttransitionBean.getVobo() == result){
//        	update = false;
//        }
        
        return checkBoxs;
	}

	
	public Integer verificarCountJoinusertransition(
			JoinPersonTransitionBean joinPersonTransitionBean)
			throws SQLException {
		
		return jdbcStksf2.queryForInt(TransitionSql.VERIFICA_COUNT_RELACION_JOINPERSONTRANSITION, new Object[]{joinPersonTransitionBean.getIdperson()});
	}

	
	public void eliminarRelacionJoinPersontransitionByIdPerson(
			Integer idperson, Integer idtransition) throws SQLException {
     
      int rows = jdbcStksf2.update(TransitionSql.ELIMINAR_JOINPERSONTRANSITION,	new Object[] { idperson,idtransition });
      

	}

	
	public void eliminarPersonalById(PersonalBean personalBean)
			throws SQLException {
		 int rows = jdbcStksf2.update(TransitionSql.ELIMINAR_PERSONAL_BY_ID,	new Object[] {personalBean.getId() });
		
	}

	
	public void insertarJoinPersonTransition(PersonalBean personalBean,
			TransitionBean transitionBean) throws SQLException {
		jdbcStksf2.update(TransitionSql.INSERT_JOINPERSONALTRANSITION, new Object[]{personalBean.getId(),transitionBean.getId()});
    
		
	}

	@Override
	public PersonalBean insertarDataPersonal(PersonalBean personalBean)
			throws SQLException {
		
		jdbcStksf2.update(TransitionSql.INSERTAR_PERSONAL, new Object[]{personalBean.getBf(), personalBean.getName(), personalBean.getEmail(), personalBean.getPhone(), personalBean.getKnkey()});
		return obtenerDataPersonalByEmail(personalBean.getEmail());
	}

	
	public PersonalBean obtenerDataPersonalByEmail(String mail)
			throws SQLException {
		  PersonalBean personalBean = new PersonalBean();

		  List<Map<String, Object>> rows = jdbcStksf2.queryForList(
				  TransitionSql.SELECCIONAR_DATAPERSONAL_BY_EMAIL,
					new Object[] { mail });
		  personalBean = new PersonalBean();
			for (Map row : rows) {
				 
	                personalBean.setId((Integer)row.get("id"));   
	                personalBean.setBf((String)row.get("bf"));    
	                personalBean.setName((String)row.get("name"));    
	                personalBean.setEmail((String)row.get("email"));    
	                personalBean.setPhone((String)row.get("phone"));   
			}
	 
	        return personalBean;
	}

	
    public JoindocumenttransitionDts actualizarVoBoEnJoindocumenttransition(JoindocumenttransitionDts joindocumenttransitionBean, String fecha) throws SQLException {
        int index = 1;
		for (Boolean jdt : joindocumenttransitionBean.getCheckboxesVoBo()) {
			jdbcStksf2.update(TransitionSql.UPDATE_VOBO_EN_JOINDOCUMENTTRANSITION, new Object[]{jdt,fecha,index++,joindocumenttransitionBean.getIdtransition()});       
		} 
        return joindocumenttransitionBean;
    }

    @Override
    public TransitionBean actualizarStatusEnTransition(TransitionBean transitionBean, Date date, String userName) throws SQLException{
    	jdbcStksf2.update(TransitionSql.UPDATE_STATUS, new Object[]{transitionBean.getStatus(), date,  userName ,transitionBean.getId()});
    	
    	
		 
		return getLastDateUpdate(transitionBean);
    
//    	transitionBean.setLastDateStatusTransition((String) jdbcStksf2.queryForObject(
//				TransitionSql.OBTENER_ULTIMA_FECHA_ESTATUS, new Object[] { date,transitionBean.getId() },
//				String.class));
    }    
  
  
    
    public TransitionBean actualizarStatusEnImplementation(TransitionBean transitionBean, String userName) throws SQLException {
    	jdbcStksf2.update(TransitionSql.UPDATE_STATUS_IMPLE, new Object[]{transitionBean.getStatus(),userName,transitionBean.getId()});
    	 return this.obtenerTransitionBeanByKnkeyAndId(transitionBean);
    }

    public PersonalBean obtenerDataPersonal(TransitionBean transitionBean) {
        PersonalBean personalBean = new PersonalBean();
        List<Map<String, Object>> rows = jdbcStksf2.queryForList(TransitionSql.SELECCIONAR_DATAPERSONAL,
					new Object[] { transitionBean.getId() });
		  personalBean = new PersonalBean();
			for (Map row : rows) {
				 
	                personalBean.setId((Integer)row.get("id"));   
	                personalBean.setBf((String)row.get("bf"));    
	                personalBean.setName((String)row.get("name"));    
	                personalBean.setEmail((String)row.get("email"));    
	                personalBean.setPhone((String)row.get("phone"));   
			}
	 
        return personalBean;
    }

	public PersonalBean obtenerDataPersonalByName(String name) {
		PersonalBean personalBean = new PersonalBean();
		List<Map<String, Object>> rows = jdbcStksf2.queryForList(
				TransitionSql.SELECCIONAR_DATAPERSONAL_BYNAME,
				new Object[] { name });
		personalBean = new PersonalBean();
		for (Map row : rows) {

			personalBean.setId((Integer) row.get("id"));
			personalBean.setBf((String) row.get("bf"));
			personalBean.setName((String) row.get("name"));
			personalBean.setEmail((String) row.get("email"));
			personalBean.setPhone((String) row.get("phone"));
		}

		return personalBean;

	}

    public ArrayList<PersonalBean> obtenerDataPersonalByKnkey(Integer knkey) {
        PersonalBean personalBean = new PersonalBean();
        ArrayList<PersonalBean> personalListbyKnkeyTransition = new ArrayList<PersonalBean>();
        List<Map<String, Object>> rows = jdbcStksf2.queryForList(
        		TransitionSql.SELECCIONAR_DATAPERSONAL_BYKNKEY,
				new Object[] { knkey });
		personalBean = new PersonalBean();
		for (Map row : rows) {
		    personalBean = new PersonalBean();
            personalBean.setId((Integer)row.get("id"));   
            personalBean.setBf((String)row.get("bf"));    
            personalBean.setName((String)row.get("name"));    
            personalBean.setEmail((String)row.get("email"));    
            personalBean.setPhone((String)row.get("phone"));    
            personalBean.setKnkey((Integer)row.get("knkey"));    
            personalListbyKnkeyTransition.add(personalBean);		
		}
     
        return personalListbyKnkeyTransition;
    }

    public PersonalBean obtenerDataPersonalById(Integer id) {
         PersonalBean personalBean = new PersonalBean();
		List<Map<String, Object>> rows = jdbcStksf2.queryForList(
				TransitionSql.SELECCIONAR_DATAPERSONAL_BYKNID,
				new Object[] { id });
		for (Map row : rows) {

			personalBean.setId((Integer)row.get("id"));   
            personalBean.setBf((String)row.get("bf"));    
            personalBean.setName((String)row.get("name"));    
            personalBean.setEmail((String)row.get("email"));    
            personalBean.setPhone((String)row.get("phone"));    
            personalBean.setKnkey((Integer)row.get("knkey"));    
		}

		return personalBean;
    }
    
    
    
    
    public DocumentsBean obtenerDocumentId(DocumentsBean documentsBean) {

    	List<Map<String, Object>> rows = jdbcStksf2.queryForList(
    			TransitionSql.SELECCIONAR_IDFROMDOCUMENTSBYNAME,
				new Object[] { documentsBean.getName() });
		for (Map row : rows) {
			documentsBean.setId((Integer)row.get("id"));
		}	
        return documentsBean;
    }

	@Override
	public TransitionBean getLastDateUpdate(TransitionBean transitionBean)
			throws SQLException {
		String sql = "select * from t_transitions where id = ?";
		 
		return (TransitionBean)jdbcStksf2.queryForObject(
				sql, new Object[] { transitionBean.getId() }, new BeanPropertyRowMapper(TransitionBean.class));
		
	
	}

	@Override
	public TransitionBean insertLastDateUpdateComment(TransitionBean transitionBean, Date date, String userName) throws SQLException {
		int row = jdbcStksf2
				.update(TransitionSql.UPDATE_LAST_DATE_COMMENT, new Object[] {date, userName,
						transitionBean.getId(), transitionBean.getKnkey() });
		return getLastDateUpdate(transitionBean);
	}


	@Override
	public TransitionBean getLastDateUpdateComment(TransitionBean transitionBean)
			throws SQLException {

		return  getLastDateUpdate(transitionBean);/*(String) jdbcStksf2.queryForObject(
				TransitionSql.OBTENER_ULTIMA_FECHA_COMMENT, new Object[] { transitionBean.getId() ,transitionBean.getKnkey()},
				String.class);*/
	}
	
	
	@Override
    public void actualizarDetailsEnJoindocumenttransition(String detailsnum, Integer documentid, Integer transitionId) {

    	int rows = jdbcStksf2.update(TransitionSql.UPDATE_DETAILS_EN_JOINDOCUMENTTRANSITION, new Object[] {
    			detailsnum, documentid, transitionId});

    }

	@Override
	public TransitionBean actualizarlastDateUpdateRequeri(Integer id, Integer knkey, String userName) throws SQLException {
		int rows = jdbcStksf2.update(TransitionSql.UPDATE_LAST_UPDATE_REQUI, new Object[] {
				userName,id, knkey});
	
		TransitionBean transitionBean = new TransitionBean();
		transitionBean.setId(id);
		return getLastDateUpdate(transitionBean);
	}
   
	@Override
	public TransitionBean getLastDateUpdateRequi(TransitionBean transitionBean)
			throws SQLException {

		return getLastDateUpdate(transitionBean);

	}
	
	@Override
	public boolean checkIfExistTransaction(int knkey) throws SQLException{
		try{
			int result = (Integer)jdbcStksf2.queryForObject(
					TransitionSql.CHECAR_EXISTENCIA_TRANSITION, new Object[] { knkey },
					Integer.class);
			if(result == 0)
				return false;
			else
				return true;
		}catch(EmptyResultDataAccessException erde){
			return false;
		}
		
	}
   
	@Override	
	public void newTransition(int knKey, UsuarioDto usuario) throws SQLException {
		String msg = usuario.getUsername() +" "+ new Date()+ " Se creo una nueva transición tipo: House Account \n" ;
        String sentenciaSQL1 = "INSERT INTO t_transitions (knkey,log,\"tipoTransition\", \"idForo\") VALUES (?,?,?,?)";
       
        String sentenciaSQL2 = "INSERT INTO joindocumenttransition (iddocument, idtransition) SELECT id, (SELECT id FROM t_transitions WHERE knkey = ? and \"tipoTransition\" = ?) FROM t_documents";
        
        String sentenciaSQL4 = "INSERT INTO t_cop (idtransitioncop, export, attachedfiles) VALUES ((SELECT id FROM t_transitions WHERE knkey = ? and \"tipoTransition\" = ?), FALSE, 0)";
        
        String sentenciaSQL5 = "INSERT INTO t_cop (idtransitioncop, export, attachedfiles) VALUES ((SELECT id FROM t_transitions WHERE knkey = ? and \"tipoTransition\" = ?), TRUE, 0)";
        
        String sentenciaForo = "INSERT INTO foro (descripcion) VALUES (?) ;";
        
        String ultimoForo = "SELECT MAX(id) FROM foro";
        
        String queryTopicosFS = "SELECT id_topico from topicos where fs='t'";
        
        String insertForoTopico = "INSERT INTO foro_topico (\"idForo\", \"idTopico\") VALUES(?,?)";
        
        
        
//        transitions FS
        String desc="Foro para FS con knKey "+ knKey;
        jdbcStksf2.update(sentenciaForo, new Object[] { desc});
        int idForo =  jdbcStksf2.queryForInt(ultimoForo);
        int rows = jdbcStksf2.update(sentenciaSQL1, new Object[] { knKey, msg ,1,idForo});
        
        List<Map<String, Object>> topicos = jdbcStksf2.queryForList(queryTopicosFS);
        
        for (Map topico : topicos) {
        	 int insert = jdbcStksf2.update(insertForoTopico, new Object[] { idForo, topico.get("id_topico")});
		}
        
        
//        transition FO
        desc="Foro para FO con knKey "+ knKey;
        jdbcStksf2.update(sentenciaForo, new Object[] { desc});
        idForo = jdbcStksf2.queryForInt(ultimoForo);
        rows = jdbcStksf2.update(sentenciaSQL1, new Object[] { knKey, msg ,2,idForo});
        topicos = jdbcStksf2.queryForList(queryTopicosFS);
        
        for (Map topico : topicos) {
        	 int insert = jdbcStksf2.update(insertForoTopico, new Object[] { idForo, topico.get("id_topico")});
		}
        
        
        
//        transition FA
        desc="Foro para FA con knKey "+ knKey;
        jdbcStksf2.update(sentenciaForo, new Object[] { desc});
        idForo = jdbcStksf2.queryForInt(ultimoForo);
        rows = jdbcStksf2.update(sentenciaSQL1, new Object[] { knKey, msg ,3,idForo});
   
        topicos = jdbcStksf2.queryForList(queryTopicosFS);
        
        for (Map topico : topicos) {
        	 int insert = jdbcStksf2.update(insertForoTopico, new Object[] { idForo, topico.get("id_topico")});
		}
        
//        documentos transitions FS
        rows = jdbcStksf2.update(sentenciaSQL2, new Object[] { knKey,1 });
//        docuemntos transition FO
        rows = jdbcStksf2.update(sentenciaSQL2, new Object[] { knKey,2 });
//        documentos transition FA
        rows = jdbcStksf2.update(sentenciaSQL2, new Object[] { knKey,3 });
        
//        cop FS (false)
        rows = jdbcStksf2.update(sentenciaSQL4, new Object[] { knKey, 1 });
//        cop FO (false)
        rows = jdbcStksf2.update(sentenciaSQL4, new Object[] { knKey, 2 });
//        cop FA (false)
        rows = jdbcStksf2.update(sentenciaSQL4, new Object[] { knKey, 3 });
        
        
//      cop FS (true)
        rows = jdbcStksf2.update(sentenciaSQL5, new Object[] { knKey ,1 });
//      cop FO (true)
        rows = jdbcStksf2.update(sentenciaSQL5, new Object[] { knKey ,2 });
//      cop FA (true)
        rows = jdbcStksf2.update(sentenciaSQL5, new Object[] { knKey ,3 });
       
    }
   
	@Override
	public void updateUploadedFile(int idFile, int idTransaction,String detail) throws SQLException{
		int rows = jdbcStksf2.update(TransitionSql.UPDATE_UPLOAD_FILE, new Object[] { detail, idFile, idTransaction });
	}
	
	@Override
	public JoindocumenttransitionDts getJoinDocumentTransaction(int idDocument, int idTransition) throws SQLException{
		JoindocumenttransitionDts joindocumenttransitionDts = new JoindocumenttransitionDts();
		List<Map<String, Object>> rows = jdbcStksf2.queryForList(
				TransitionSql.SELECCIONAR_IDSFROMJOINDOCUMENTTRANSITION,
				new Object[] { idDocument , idTransition});
		for (Map row : rows) {
			joindocumenttransitionDts.setId((Integer)row.get("id"));
			joindocumenttransitionDts.setIddocument((Integer)row.get("iddocument"));
			joindocumenttransitionDts.setIdtransition((Integer)row.get("idtransition"));
			joindocumenttransitionDts.setVobo((Boolean)row.get("vobo"));
			joindocumenttransitionDts.setUploaded((Boolean)row.get("uploaded"));
			joindocumenttransitionDts.setDetails((String)row.get("details"));
		}
		
		return joindocumenttransitionDts;
	}
  
	@Override
	public void updateJoinTransitionDocuments(Map<Integer,Boolean> checkBoxs, int idTransition) throws SQLException{
		Iterator it = checkBoxs.entrySet().iterator();
		 
	    while (it.hasNext()) {
	        Map.Entry e = (Map.Entry)it.next();
//	        if((boolean) e.getValue()){
	        	 int rows = jdbcStksf2.update(
	 					TransitionSql.UPDATE_VOBO_CHECKBOX,
	 					new Object[] {e.getValue(),	e.getKey(),idTransition });
//	        }
	       

	   }
	}

	@Override
	public void insertHistorico(HistoricoDto historico) throws SQLException{
		String query = "INSERT INTO historico (\"idUsuario\", \"fechaAccion\", accion, \"idAccion\") VALUES (?, ? ,?,?)";
		int rows = jdbcStksf2
				.update(query, new Object[] {
						historico.getIdUsuario(), historico.getFechaAccion(), historico.getAccion(), historico.getIdAccion()});

	}
	
	
	
	@Override
	public List<JoindocumenttransitionDts> getEstatusAndDateDocuments(TransitionBean transition) throws SQLException{
		String sql = "SELECT \"id\","+ 
							"iddocument,"+
							"(SELECT  t_documents.\"name\" FROM t_documents where id=joindoc.iddocument) as name,"+
							"idtransition,"+
							"vobo,"+
							"uploaded,"+
							"completiondate,"+
							"details"+
					" FROM joindocumenttransition as joindoc"+ 
					" WHERE idtransition = ? order by iddocument";
		 
		List<JoindocumenttransitionDts> status = jdbcStksf2.query(
				sql, new Object[] {transition.getId()},new BeanPropertyRowMapper(JoindocumenttransitionDts.class));
	 
		return status;
	}
	
	
	@Override
	public List<PersonOptions> getPersonalAdd(TransitionBean transition) throws SQLException{
		
		String sql ="SELECT A.\"name\" as displayText, A.id as value " +
						" FROM t_personal  A"+
						" LEFT JOIN (SELECT tp.*"+ 
									" FROM joinpersontransition jpt"+ 
									" INNER JOIN  t_personal tp"+
									" ON jpt.idpersona = tp.id"+ 
									" WHERE jpt.idtransition_p = ?)  B"+
						" on(a.\"id\"  = b.id)"+
						" where b.id is Null";

		List<PersonOptions> options = jdbcStksf2.query(
				sql, new Object[] {transition.getId()},new BeanPropertyRowMapper(PersonOptions.class));
	 
		return options;
		
		
	}

	@Override
	public TransitionBean setLastDateUpdateSubmitFileImplementation(TransitionBean transition, Date date, String userName)throws SQLException{
		
		String sql ="update t_transitions set \"lastDateUpdateSubmitFileTransition\" = ?, \"lastUserUpdateSubmitFileTransition\" = ? where id = ?";
		int rows = jdbcStksf2.update(sql, new Object[] { date, userName, transition.getId() });
		return getLastDateUpdate(transition);
	}

}


